package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.Position;
import com.example.demo.outstyle.*;

import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PositionMapper extends BaseMapper<Position> {

    @Select("select " +
            "ifnull(count(*), 0) as positionCount, " +
            "round(ifnull(avg(avg_salary), 0) * 1000, 0) as salaryAvg, " +
            "ifnull(count(distinct company_fullname), 0) as companyCount " +
            "from position where status = '1';")
    Base selectBaseData();

    @Select("select value " +
            "from github" +
            " order by  " +
            "(case when item = 'Java' then 1  " +
            " when item = 'php' then 2 " +
            " when item = 'c++' then 3 " +
            " when item = 'ios' then 4 " +
            " when item = 'python' then 5 " +
            " when item = 'js' then 6 " +
            "end " +
            ")")
    @Results(id = "language", value = {
            @Result(column = "value")
    })
    List<Integer> selectLanguageCounts();
    @Select("select city, count(*) as numbers, avg(longitude) as longitude,avg(latitude) as latitude from position where status = '1' group by city order by numbers desc ")
    @Results(id = "city", value = {
            @Result(column = "city", property = "name"),
            @Result(column = "numbers", property = "count"),
            @Result(column = "longitude", property = "longitude"),
            @Result(column = "latitude", property = "latitude")
    })
    List<CityMapBase> selectPositionByCity();

    @Select("select round(avg(avg_salary)*1000, 0) from position where workyear in ('1', '2', '3', '4', '5', '6') and status = '1' group by workyear order by workyear")
    List<Integer> selectSalaryByWorkYear();

    @Select("select company_label_list from position where company_label_list not in ('NULL', '') and status = '1';")
    List<String> selectAdvantageWords();

    @Select("<script>select * from position where status = '1' " +
            "<if test=\" example != null and example.positionName != null and example.positionName != '' \">and position_name like \"%\"#{example.positionName}\"%\" </if>" +
            "<if test=\" example != null and example.company != null and example.company != '' \">and company_fullname like \"%\"#{example.company}\"%\" </if>" +
            "<if test=\" example != null and example.location !=null and example.location != '' \">and city = #{example.location} or district = #{example.location}</if>" +
            "<if test=\" example != null and example.type !=null and example.type != '' \">and first_type = #{example.type} or second_type = #{example.type} or third_type = #{example.type} </if>" +
            "<if test=\" example != null and example.slider_begin !=null and example.slider_end != null \">and avg_salary between  #{example.slider_begin}  and  #{example.slider_end}</if>" +
            "<if test=\" example != null and begin != -1 and limit != -1 \"> Limit #{begin}, #{limit}</if>" +
            "</script>")
    List<Position> selectAll(@Param("begin")int begin,
                             @Param("limit")int limit,
                             @Param("example") Position example);

    @Select("<script> select distinct company_fullname from position  where status = '1' " +
            "<foreach collection='list' item ='item' open ='and' separator=' and ' close=''> " +
            "company_fullname like \"%\"#{item}\"%\" " +
            "</foreach> " +
            "Limit 0, 10" +
            "</script>")
    List<String> selectCompany(@Param("list") List<String> keyWordList);

    @Select("<script> select distinct position_name from position  where status = '1' " +
            "<foreach collection='list' item ='item' open ='and' separator=' and ' close=''> " +
            "position_name like \"%\"#{item}\"%\" " +
            "</foreach> " +
            "Limit 0, 10" +
            "</script>")
    List<String> selectPosition(@Param("list") List<String> keyWordList);
    /*
        分割线
     */
    @Select("select distinct company_logo_url as logo " +
            "from position " +
            "where company_logo_url is not null and company_logo_url != \"https://www.lgstatic.com/thumbnail_120x120/images/logo_default.png\"  and status = '1' " +
            "order by publish_time Limit 20") //+
            //"Limit #{begin}, #{limit} ")
    @Results(id = "logo", value = {
            @Result(column = "logo")
    })
    List<String> selectCompanyLogo(@Param("begin")int begin, @Param("limit")int limit);//企业Logo轮播
    @Select("SELECT " +
            " CASE " +
            "WHEN third_type IN ('Java') THEN " +
            " 'Java' " +
            "WHEN third_type LIKE '%PHP%' THEN " +
            " 'PHP' " +
            "WHEN third_type LIKE '%C%' THEN " +
            " 'C++' " +
            "WHEN third_type LIKE '%iOS%' THEN " +
            " 'iOS' " +
            "WHEN third_type LIKE '%Python%' THEN " +
            " 'Python' " +
            "WHEN ( " +
            " (third_type LIKE '%Javascript%') " +
            " OR (third_type LIKE '%前端%') " +
            " OR (third_type LIKE '%Web%') " +
            ") THEN " +
            " 'JavaScript' " +
            "ELSE " +
            " third_type " +
            "END AS types, " +
            " count(*) AS counts " +
            "FROM " +
            " position " +
            "WHERE " +
            " STATUS = '1' " +
            "AND ( " +
            " third_type LIKE '%Java%' " +
            " OR third_type LIKE '%PHP%' " +
            " OR third_type LIKE '%C%' " +
            " OR third_type LIKE '%iOS%' " +
            " OR third_type LIKE '%Python%' " +
            " OR third_type LIKE '%前端%' " +
            ") " +
            "GROUP BY " +
            " types " +
            "ORDER BY " +
            " ( " +
            "  CASE " +
            "  WHEN types = 'Java' THEN " +
            "   1 " +
            "  WHEN types = 'PHP' THEN " +
            "   2 " +
            "  WHEN types = 'C++' THEN " +
            "   3 " +
            "  WHEN types = 'iOS' THEN " +
            "   4 " +
            "  WHEN types = 'Python' THEN " +
            "   5 " +
            "  WHEN types = 'JavaScript' THEN " +
            "   6 " +
            "  END " +
            " )")
    @Results(id = "language1", value = {
            @Result(column = "counts")
    })
    List<Integer> selectPositionByLanguage();//开发语言招聘量
    @Select("select COUNT(CASE WHEN  avg_salary <= 5 THEN 1 ELSE NULL END ) AS cnt0," +
            "COUNT(CASE WHEN avg_salary > 5 AND avg_salary <= 10 THEN 1 ELSE NULL END ) AS cnt1," +
            "COUNT(CASE WHEN avg_salary > 10 AND avg_salary <= 15 THEN 1 ELSE NULL END ) AS cnt2," +
            "COUNT(CASE WHEN avg_salary > 15 AND avg_salary <= 20 THEN 1 ELSE NULL END ) AS cnt3," +
            "COUNT(CASE WHEN avg_salary > 20 AND avg_salary <= 25 THEN 1 ELSE NULL END ) AS cnt4," +
            "COUNT(CASE WHEN avg_salary > 25 AND avg_salary <= 30 THEN 1 ELSE NULL END ) AS cnt5," +
            "COUNT(CASE WHEN avg_salary > 30  THEN 1 ELSE NULL END ) AS cnt6 " +
            "from position where status = '1'"
            )
    @Results(id = "salary",value={
            @Result(column = "cnt0", property = "cnt0"),
            @Result(column = "cnt1", property = "cnt1"),
            @Result(column = "cnt2", property = "cnt2"),
            @Result(column = "cnt3", property = "cnt3"),
            @Result(column = "cnt4", property = "cnt4"),
            @Result(column = "cnt5", property = "cnt5"),
            @Result(column = "cnt6", property = "cnt6")}
           )
    Cnt selectPositionBySalary();//薪资分布
    @Select("select avg_salary from position where status = '1'")
    List<Integer> selectPositionBySalaryTest();
    @Select("select count(company_size) as counts " +
            "from position where status = '1' " +
            "group by company_size  order by company_size ")
    @Results(id = "selectCompanyBySize", value = {
            @Result(column = "counts")
    })
    List<Integer> selectCompanyBySize();//企业规模分布
    @Select("select CONCAT(city,district) as name,count(district) as company_count,sum(company_num)as position_count,avg(avg_salary)*1000 as salary_avg " +
            "FROM (select city,district,count(id) as company_num,avg(avg_salary)as avg_salary " +
            "     from position where status = '1' " +
            "     group by city,district,company_fullname  HAVING district is NOT null) a " +
            "group by city,district " +
            "order by position_count DESC,salary_avg   " +
            "limit 10 ")
    @Results(id = "Top5ByDistrict", value = {
            @Result(column = "name", property = "name"),
            @Result(column = "company_count", property = "companyCount"),
            @Result(column = "position_count", property = "positionCount"),
            @Result(column = "salary_avg", property = "salaryAvg")
    })
    List<District> selectTop5ByDistrict();//行政区划Top10
    @Select("select count(education) as counts " +
            "from position where status = '1' " +
            "group by education  " +
            "order by education ")
    @Results(id = "selectPositionByEducation", value = {
            @Result(column = "counts")
    })
    List<Integer> selectPositionByEducation();//学历要求

    @Select("<script>select count(*) from position where status = '1' " +
            "<if test=\" example.company !=null and example.company != '' \">and company_fullname like \"%\"#{example.company}\"%\" </if>" +
            "<if test=\" example.location !=null and example.location != '' \">and city = #{example.location} or district = #{example.location}</if>" +
            "<if test=\" example.type !=null and example.type != '' \">and first_type = #{example.type} or second_type = #{example.type} or third_type = #{example.type} </if>" +
            "<if test=\" example.slider_begin !=null and example.slider_end != null \">and avg_salary between  #{example.slider_begin}  and  #{example.slider_end}</if>" +
            "</script>")
    int selectAllCount(@Param("begin")int begin,
                             @Param("limit")int limit,
                             @Param("example") Position example);


    @Select("select city, district from position where status = '1' ")
    List<Position> selectAllLocation();

    @Select("select first_type, second_type, third_type  from position where status = '1' ")
    List<Position> selectAllType();

    @Select("SELECT " +
            "ifnull(count(*), 0) AS companyByFamous, " +
            "ROUND(ifnull(avg(avg_salary), 0)* 1000 , 0) AS salaryByFamous " +
            "FROM " +
            "position " +
            "WHERE " +
            "STATUS = '1' " +
            "GROUP BY " +
            "company_famous " +
            "ORDER BY " +
            "company_famous DESC")
    List<ByFamous> selectByFamous();

    @Select("SELECT " +
            "count(*) AS companyByFamous, " +
            "ROUND(avg(avg_salary)*1000) AS salaryByFamous " +
            "FROM " +
            "position " +
            "WHERE " +
            "STATUS = '1' and company_famous = '0' "
    )
    ByFamous selectByNoFamous();
    @Select("SELECT " +
            "count(*) " +
            "FROM " +
            "position " +
            "WHERE " +
            "status = '1' and company_famous = #{flag} " +
            "GROUP BY " +
            "workyear " +
            "ORDER BY " +
            "workyear")
    List<Integer> selectWorkYearByFamous(int flag);
}
